/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Sql_package;

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import java.sql.*;

/**
 *
 * @author lemarcha
 */
public class JDBCProgram {

	static String url = "jdbc:hsqldb:file:database";
	static Statement stmt;
	static Connection my_connection;
        ResultSet results;
        
	public static void main(String args[]){

            //main
	}

//----------- Etablissement de la connexion---------------------

        public static Connection getConnection(String user, String password){

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");	//Class.forName("myDriver.ClassName"); ?

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			my_connection = DriverManager.getConnection(url,
				 user, password);

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}


                getRole(user,password);

                
		return my_connection;
	}

/*CREATE TABLE Profil(login VARCHAR(30) NOT NULL, mdp VARCHAR(10) NOT NULL,role VARCHAR(11) NOT NULL,idProfesseur INTEGER NOT NULL,
PRIMARY KEY (login),
FOREIGN KEY (idProfesseur) REFERENCES Professeur(idProfesseur),
CHECK role like('Responsable');*/

// ------------------- Renvoi du role de la personne connecte --------------
        public static ResultSet getRole(String userid, String password){

            ResultSet my_result_requete;
            my_result_requete = null;


        try {
            Statement statement = my_connection.createStatement();
            my_result_requete = statement.executeQuery("SELECT role FROM" + "Profil WHERE login='" + userid + "'AND mdp='" + password + "';");

        } catch (SQLException ex) {
            Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
        }
        
       return my_result_requete;
 }

//-------------Statement statement = connexion.createStatement() ;

        	/*Id            Name            Annee
	 	22              Reseaux         1
	 	5768 		Be_POO          2
	 	1234 		Automates       3
	 	5678 		Anglais         4*/


/*CREATE TABLE Professeur (idProfesseur INTEGER NOT NULL, Nom VARCHAR(20) NOT NULL, Prenom VARCHAR(20) NOT NULL, naissance DATE NOT NULL, Specialite VARCHAR(20) NULL,
PRIMARY KEY (idProfesseur));*/

  //------------Insertion table prof---------------
      public static void insertion_table_prof(int idProfesseur, String nom, String prenom, String naissance, String specialite){

          //Connection con1 = getConnection();
          // ma connexion s appel my_connection

          String inserProf = "insert into cours values ('" + idProfesseur + "','" +
                  nom + "','" + prenom + "','" + naissance + "','" + specialite +"');";

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserProf);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table prof OK");
      }


//------------Insertion table cours-----------------
      public static void insertion_table_cours(String nom_cours,int annee)
	{

          //Connection con2 = getConnection();
          // ma connexion s appel my_connection

          String inserCoursTest = "insert into cours values ('" +
                  nom_cours + "','" + annee + "');";
         
          //String insertCours;
	  //insertCours = "insert into cours values(6323, 'Reseaux','1')";
          //"INSERT INTO cours (nom) VALUES ('Nul')";

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserCoursTest);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table cours OK");
      }
        
/*CREATE TABLE Seance(idCours INTEGER NOT NULL, idSeance INTEGER NOT NULL, nomSeance VARCHAR(10) NOT NULL,nbretudiant INTEGER NULL,
PRIMARY KEY (idSeance),
FOREIGN KEY (idCours) REFERENCES Cours(idCours));*/

//--------Insertion table séance-------------
      public static void insertion_table_seance(int id_Seance, String nom_seance,int numero_cours){

          //Connection con3 = getConnection();

          String inserSeance = "insert into cours values ('" + id_Seance + "','" +
                  nom_seance + "','" + numero_cours + "');";
         
                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserSeance);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table seance OK");
      }


      //--------Insertion table questions-------------
      public static void insertion_table_question(int id_Questions, String ma_question){

          //Connection con4 = getConnection();

          String inserQuestion = "insert into cours values ('" + id_Questions + "','" +
                  ma_question + "');";

          System.out.println(inserQuestion);

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserQuestion);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table seance OK");

      }


      //--------------------Insertion table reponses------------------------
      public static void insertion_table_reponse(int id_Q, int id_R, boolean correct)
	{

          //Connection con5 = getConnection();

          String inserReponse = "insert into cours values ('" + id_Q + "','" +
                  id_R + "','"+ correct +"');";


          System.out.println(inserReponse);

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserReponse);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table question OK");
      }

       //--------------------Insertion table cours Prof------------------------
      public static void insertion_table_cours_prof(int id_Cours, int id_Prof){

          //Connection con6 = getConnection();

          String inserCours_prof = "insert into cours values ('" + id_Cours + "','" +
                  id_Prof +"');";


          System.out.println(inserCours_prof);

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(inserCours_prof);
			stmt.close();
			my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Insertion table question OK");
      }


/*SELECT c.nom,c.annee
FROM cours c, professeur p , coursprofesseur cp, profil l
WHERE c.idcours = cp.idcours AND p.idprofesseur = cp.idprofesseur AND cp.idprofesseur = l.idprofesseur AND l.login = '';*/


// ------------------- Envoi de la liste des cours de la personne connectee --------------
 public static ResultSet Liste_cours(String userid){

        ResultSet my_result_requete;
        my_result_requete = null;

    try {
        Statement statement = my_connection.createStatement();
        my_result_requete = statement.executeQuery("SELECT c.nom,c.annee" +
                " FROM" + "cours c, professeur p, coursprofesseur cp, profil l" +
                " WHERE c.idcours = cp.idcours AND p.idprofesseur = cp.idprofesseur" +
                "AND cp.idprofesseur = l.idprofesseur AND" +
                " l.login = '" + userid + "';");

        } catch (SQLException ex) {
            Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
        }

        return my_result_requete;
 }



/*SELECT nomSeance
FROM Seance
WHERE idCours IN (SELECT idCours FROM cours WHERE nom='' AND annee=);*/

 // ------------------- Envoi de la liste des seances d un cours --------------
 public static ResultSet Liste_seance(String nom_cours, String annee){

            ResultSet my_result_requete;
            my_result_requete = null;

            try {
                Statement statement = my_connection.createStatement();
                my_result_requete = statement.executeQuery("SELECT nomSeance FROM"
                        + "Seance WHERE idCours IN (SELECT idCours FROM cours WHERE"
                        + "nom = '" + nom_cours + "' AND annee ='" + annee + "';");

            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }

            return my_result_requete;
 }

/*SELECT question
FROM Question
WHERE idSeance IN (SELECT idSeance FROM Seance WHERE nomSeance='');*/

 // ------------------- Envoi de la liste des questions d une seance donnee --------------

 public static ResultSet Liste_questions(String nom_seance){

            ResultSet my_result_requete;
            my_result_requete = null;

            try {
                Statement statement = my_connection.createStatement();
                my_result_requete = statement.executeQuery("SELECT question FROM"
                        + "Question WHERE idSeance IN (SELECT idSeance FROM Seance " +
                        "WHERE nomSeance = '" + nom_seance + "';");

            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }

            return my_result_requete;
 }


 /*question precise avec les reponses associees
SELECT q.question,r.reponse,r.resultat
FROM reponse r, question q, seance s
WHERE r.idQuestion = q.idquestion AND q.idseance = s.idseance AND
q.question='' AND s.nomSeance='';*/

 
 // ------------------- Envoi d'une question precise avec ces reponses --------------
 public static ResultSet Envoi_QR_precise(String question, String nom_seance){

            ResultSet my_result_requete;
            my_result_requete = null;
        try {
            Statement statement = my_connection.createStatement();
            my_result_requete = statement.executeQuery("SELECT q.question," +
                    "r.reponse, r.resultat FROM reponse r, question q, seance s" +
                    "WHERE r.idQuestion = q.idquestion AND " +
                    "q.idseance = s.idseance AND q.question= '" + question + "'AND s.nomSeance='" + nom_seance + "';");

        } catch (SQLException ex) {
            Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
        }
       return my_result_requete;
 }




/*DELETE FROM question WHERE idSeance IN (SELECT idSeance FROM seance WHERE
idcours IN(SELECT idCours FROM Cours WHERE nom='d' AND annee=323));

DELETE FROM seance WHERE idcours IN(SELECT idCours FROM Cours WHERE
nom='d' AND annee=323);
 *
DELETE FROM cours WHERE nom='d' AND annee=323;*/

  //-----Suppression d un cours donné (Et donc des questions et des seances s y trouvant)-------------
      public static void suppression_cours(String nom_cours, String annee){

          //Connection con4 = getConnection();

          String del_Cours1 = "DELETE FROM question WHERE" +
                    " idSeance IN (SELECT idSeance FROM seance WHERE" +
                    "idcours IN(SELECT idCours FROM Cours WHERE " +
                    "nom ='" + nom_cours + "'AND annee='" + annee + "';";


          String del_Cours2 = "DELETE FROM seance WHERE idcours IN " +
                  "(SELECT idCours FROM Cours " +
                  "WHERE nom ='" + nom_cours + "'AND annee='" + annee + "';";

          
          String del_Cours3 = "DELETE FROM cours WHERE " +
                  "nom ='" + nom_cours + "'AND annee='" + annee + "';";

          //System.out.println(del_Cours1);

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(del_Cours1);
                        stmt.executeUpdate(del_Cours2);
                        stmt.executeUpdate(del_Cours3);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Suppresion du cours: OK");

      }




/*DELETE FROM question WHERE idSeance IN (SELECT idSeance FROM seance WHERE
nomSeance='');
DELETE FROM seance WHERE nomSeance='';*/

  //------Suppression d une seance donnee ( et donc des questions s y trouvant)-------------
      public static void suppression_seance(String nom_seance){

          //Connection con4 = getConnection();

          String del_Seance1 = "DELETE FROM question WHERE idSeance IN " +
                  "(SELECT idSeance FROM seance " +
                  "WHERE nomSeance ='" + nom_seance + "';";


          String del_Seance2 = "DELETE FROM seance WHERE " +
                  "nomSeance ='" + nom_seance + "';";

          //System.out.println(del_Seance1);

                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(del_Seance1);
                        stmt.executeUpdate(del_Seance2);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Suppresion du cours: OK");

      }



/*Supprimer question
DELETE FROM question WHERE question='' AND idSeance IN (SELECT idSeance
FROM seance WHERE nomSeance='');*/

   //------Suppression d une seance donnee ( et donc des questions s y trouvant)-------------
      public static void suppression_question(String nom_seance, String nom_question){

          //Connection con4 = getConnection();

            String del_Question1 = "DELETE FROM question WHERE " +
                    "question ='" +  nom_question + "' AND idSeance IN" +
                    "(SELECT idSeance FROM seance " +
                    "WHERE nomSeance ='" + nom_seance + "';";

          //System.out.println(del_Question1);
                try {
			stmt = my_connection.createStatement();
	   		stmt.executeUpdate(del_Question1);
			stmt.close();
			//my_connection.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		JOptionPane.showMessageDialog(null,"Suppresion du cours: OK");

      }



//---------------- Fermeture de la base de donnes ------------------------
        public static void Close(){

                // Fermeture -- Deconnexion de la base de donnees

                try{

                    stmt = my_connection.createStatement();
                    stmt.executeQuery("SHUTDOWN");
                    stmt.close();
                    stmt.close();
                    my_connection.close();

                 }
                 catch(Exception e){
                  System.out.println("echec lors de la fermeture:"+e.getMessage());

                 }
        }

//------------------------ Verification de non-existence du cours ---------------------

        /*Si le cours existe déjà
        SELECT nom,annee
        FROM cours
        WHERE nom='' AND annee=;*/

    public int requete_existence_cours (String nomCours,String anneeCours){

                // recuperation de resultat
                Statement statement = null;
                ResultSet my_result_requete;
                my_result_requete = null;
                int resultat_existence_cours;
                resultat_existence_cours = -1;

            try {
                statement = my_connection.createStatement();
            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                //ResultSet resultat = statement.executeQuery("SELECT * FROM table WHERE condition");
                // requete permettant de recuperer toutes les questions d une seance precise
                my_result_requete = statement.executeQuery("SELECT nom,annee FROM cours WHERE nom='" +
                        nomCours + "' AND annee='"+ anneeCours + "';");

            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }

        if(my_result_requete == null){
            resultat_existence_cours = 0;
        }

        return resultat_existence_cours;

   }


  //------------------------ Verification de non-existence de la seance ---------------------

    /*SELECT idseance
    FROM Seance
    WHERE idcours IN (SELECT idcours
    FROM cours
    WHERE nom='' AND annee=)
    AND idseance IN (SELECT idseance FROM seance WHERE nomseance='');*/

    public int requete_existence_seance (String nomCours,String anneeCours,String nomseance){

                // recuperation de resultat
                Statement statement = null;
                ResultSet my_result_requete;
                my_result_requete = null;
                int resultat_existence_seance;
                resultat_existence_seance = -1;

            try {
                statement = my_connection.createStatement();
            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                //ResultSet resultat = statement.executeQuery("SELECT * FROM table WHERE condition");
                // requete permettant de recuperer toutes les questions d une seance precise
                my_result_requete = statement.executeQuery("SELECT idseance FROM seance" +
                        " WHERE idcours IN (SELECT idcours FROM cours WHERE nom='" +
                        nomCours + "' AND annee='"+ anneeCours + "') " +
                        "AND idseance IN (SELECT idseance FROM seance " +
                        "WHERE nomseance='" + nomseance + "';");

            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);

            }

        if(my_result_requete == null){
            resultat_existence_seance = 0;
        }

        return resultat_existence_seance;
   }

      
//------------------------ Verification de non-existence de la question ---------------------

/*Si la question existe déjà dans la séance
SELECT idquestion FROM question WHERE idSeance IN (SELECT idseance
FROM seance WHERE nomseance='') AND question = '');*/

    public int requete_existence_question (int idquestion, String nomSeance){

                // recuperation de resultat
                Statement statement = null;
                ResultSet my_result_requete;
                my_result_requete = null;
                int resultat_existence_question;
                resultat_existence_question = -1;

            try {
                statement = my_connection.createStatement();
            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                //ResultSet resultat = statement.executeQuery("SELECT * FROM table WHERE condition");
                // requete permettant de recuperer toutes les questions d une seance precise
                my_result_requete = statement.executeQuery("SELECT idquestion FROM question" +
                        " WHERE idseance IN (SELECT idseance FROM seance WHERE nomseance='" +
                        nomSeance + "' AND question='"+ idquestion +"';");


            } catch (SQLException ex) {
                Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);

            }

        if(my_result_requete == null){
            resultat_existence_question = 0;
        }

        return resultat_existence_question;
   }

    /*
       public ResultSet requete (String sql_requete){

            // recuperation de resultat
            Statement statement = null;
            ResultSet my_result_requete;
            my_result_requete = null;
            
        try {
            statement = my_connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //ResultSet resultat = statement.executeQuery("SELECT * FROM table WHERE condition");
            // requete permettant de recuperer toutes les questions d une seance precise
            my_result_requete = statement.executeQuery(sql_requete);
            my_result_requete = statement.executeQuery("SELECT * FROM Questions WHERE ID_S=ID_Q;");
        } catch (SQLException ex) {
            Logger.getLogger(JDBCProgram.class.getName()).log(Level.SEVERE, null, ex);

        }
                
    //  results = new ResultSet(statement.executeQuery(sql_requete));
    
                // requete permettant de recupererr les differentes reponses pour une question precise
                ResultSet resultat = statement.executeQuery("SELECT reponse_str FROM Reponse WHERE ID_Question=Id_Q")
                 
    return my_result_requete;

    }*/

}//End of class
